package com.example.huixue.mapper;

import com.example.huixue.entity.Resource;
import org.apache.ibatis.annotations.*;

import java.util.List;

@Mapper
public interface ResourceMapper {
    
    /**
     * 分页查询资源
     */
    @Select("<script>" +
            "SELECT r.*, c.category_name, u.username " +
            "FROM resources r " +
            "LEFT JOIN categories c ON r.category_id = c.category_id " +
            "LEFT JOIN users u ON r.uploader_id = u.user_id " +
            "WHERE r.status = 'APPROVED' " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (r.title LIKE CONCAT('%', #{keyword}, '%') OR r.description LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "<if test='categoryId != null'>" +
            "AND r.category_id = #{categoryId} " +
            "</if>" +
            "<if test='fileType != null and fileType != \"\"'>" +
            "AND r.file_type = #{fileType} " +
            "</if>" +
            "ORDER BY " +
            "<choose>" +
            "<when test='sortBy == \"downloadCount\"'>r.download_count DESC</when>" +
            "<when test='sortBy == \"rating\"'>r.rating DESC</when>" +
            "<when test='sortBy == \"viewCount\"'>r.view_count DESC</when>" +
            "<otherwise>r.created_at DESC</otherwise>" +
            "</choose>" +
            " LIMIT #{offset}, #{size}" +
            "</script>")
    List<Resource> selectResourcesWithPagination(@Param("offset") int offset, 
                                                 @Param("size") int size,
                                                 @Param("keyword") String keyword,
                                                 @Param("categoryId") Long categoryId,
                                                 @Param("fileType") String fileType,
                                                 @Param("sortBy") String sortBy);
    
    /**
     * 计算查询结果总数
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM resources r " +
            "WHERE r.status = 'APPROVED' " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (r.title LIKE CONCAT('%', #{keyword}, '%') OR r.description LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "<if test='categoryId != null'>" +
            "AND r.category_id = #{categoryId} " +
            "</if>" +
            "<if test='fileType != null and fileType != \"\"'>" +
            "AND r.file_type = #{fileType} " +
            "</if>" +
            "</script>")
    long countResources(@Param("keyword") String keyword,
                        @Param("categoryId") Long categoryId,
                        @Param("fileType") String fileType);
    
    /**
     * 根据ID查询资源
     */
    @Select("SELECT r.*, c.category_name, u.username " +
            "FROM resources r " +
            "LEFT JOIN categories c ON r.category_id = c.category_id " +
            "LEFT JOIN users u ON r.uploader_id = u.user_id " +
            "WHERE r.resource_id = #{resourceId}")
    Resource selectById(Long resourceId);
    
    /**
     * 根据分类ID查询资源
     */
    @Select("SELECT r.*, c.category_name, u.username " +
            "FROM resources r " +
            "LEFT JOIN categories c ON r.category_id = c.category_id " +
            "LEFT JOIN users u ON r.uploader_id = u.user_id " +
            "WHERE r.category_id = #{categoryId} AND r.status = 'APPROVED' " +
            "ORDER BY r.created_at DESC " +
            "LIMIT #{limit}")
    List<Resource> selectByCategoryId(@Param("categoryId") Long categoryId, @Param("limit") int limit);
    
    /**
     * 计算所有资源总数
     */
    @Select("SELECT COUNT(*) FROM resources")
    long countAllResources();
    
    /**
     * 根据状态计算资源数量
     */
    @Select("SELECT COUNT(*) FROM resources WHERE status = #{status}")
    long countResourcesByStatus(@Param("status") String status);
    
    /**
     * 获取总下载量
     */
    @Select("SELECT COALESCE(SUM(download_count), 0) FROM resources")
    long getTotalDownloads();
    
    /**
     * 获取总浏览量
     */
    @Select("SELECT COALESCE(SUM(view_count), 0) FROM resources")
    long getTotalViews();
    
    /**
     * 更新资源状态
     */
    @Update("UPDATE resources SET status = #{status} WHERE resource_id = #{resourceId}")
    int updateResourceStatus(@Param("resourceId") Long resourceId, @Param("status") String status);
    
    /**
     * 插入资源
     */
    @Insert("INSERT INTO resources (title, description, file_path, file_size, file_type, category_id, uploader_id, status) " +
            "VALUES (#{title}, #{description}, #{filePath}, #{fileSize}, #{fileType}, #{categoryId}, #{uploaderId}, #{status})")
    @Options(useGeneratedKeys = true, keyProperty = "resourceId")
    int insert(Resource resource);
    
    /**
     * 更新资源
     */
    @Update("UPDATE resources SET title = #{title}, description = #{description}, category_id = #{categoryId}, status = #{status} " +
            "WHERE resource_id = #{resourceId}")
    int update(Resource resource);
    
    /**
     * 删除资源
     */
    @Delete("DELETE FROM resources WHERE resource_id = #{resourceId}")
    int delete(Long resourceId);
    
    /**
     * 增加下载次数
     */
    @Update("UPDATE resources SET download_count = download_count + 1 WHERE resource_id = #{resourceId}")
    int incrementDownloadCount(Long resourceId);
    
    /**
     * 增加查看次数
     */
    @Update("UPDATE resources SET view_count = view_count + 1 WHERE resource_id = #{resourceId}")
    int incrementViewCount(Long resourceId);
    
    /**
     * 更新评分
     */
    @Update("UPDATE resources SET rating = #{rating} WHERE resource_id = #{resourceId}")
    int updateRating(@Param("resourceId") Long resourceId, @Param("rating") Double rating);
    
    /**
     * 获取热门资源
     */
    @Select("SELECT r.*, c.category_name, u.username " +
            "FROM resources r " +
            "LEFT JOIN categories c ON r.category_id = c.category_id " +
            "LEFT JOIN users u ON r.uploader_id = u.user_id " +
            "WHERE r.status = 'APPROVED' " +
            "ORDER BY r.download_count DESC, r.view_count DESC " +
            "LIMIT #{limit}")
    List<Resource> selectPopularResources(@Param("limit") int limit);
    
    /**
     * 获取最新资源
     */
    @Select("SELECT r.*, c.category_name, u.username " +
            "FROM resources r " +
            "LEFT JOIN categories c ON r.category_id = c.category_id " +
            "LEFT JOIN users u ON r.uploader_id = u.user_id " +
            "WHERE r.status = 'APPROVED' " +
            "ORDER BY r.created_at DESC " +
            "LIMIT #{limit}")
    List<Resource> selectLatestResources(@Param("limit") int limit);
    
    /**
     * 根据用户ID查询上传的资源
     */
    @Select("SELECT r.*, c.category_name " +
            "FROM resources r " +
            "LEFT JOIN categories c ON r.category_id = c.category_id " +
            "WHERE r.uploader_id = #{uploaderId} " +
            "ORDER BY r.created_at DESC")
    List<Resource> selectByUploaderId(Long uploaderId);
    
    /**
     * 管理员分页查询资源（包含所有状态）
     */
    @Select("<script>" +
            "SELECT r.*, c.category_name, u.username " +
            "FROM resources r " +
            "LEFT JOIN categories c ON r.category_id = c.category_id " +
            "LEFT JOIN users u ON r.uploader_id = u.user_id " +
            "WHERE 1=1 " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (r.title LIKE CONCAT('%', #{keyword}, '%') OR r.description LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "<if test='categoryId != null'>" +
            "AND r.category_id = #{categoryId} " +
            "</if>" +
            "<if test='fileType != null and fileType != \"\"'>" +
            "AND r.file_type = #{fileType} " +
            "</if>" +
            "<if test='status != null and status != \"\"'>" +
            "AND r.status = #{status} " +
            "</if>" +
            "ORDER BY " +
            "<choose>" +
            "<when test='sortBy == \"downloadCount\"'>r.download_count DESC</when>" +
            "<when test='sortBy == \"rating\"'>r.rating DESC</when>" +
            "<when test='sortBy == \"viewCount\"'>r.view_count DESC</when>" +
            "<otherwise>r.created_at DESC</otherwise>" +
            "</choose>" +
            " LIMIT #{offset}, #{size}" +
            "</script>")
    List<Resource> selectAdminResourcesWithPagination(@Param("offset") int offset, 
                                                      @Param("size") int size,
                                                      @Param("keyword") String keyword,
                                                      @Param("categoryId") Long categoryId,
                                                      @Param("fileType") String fileType,
                                                      @Param("status") String status,
                                                      @Param("sortBy") String sortBy);
    
    /**
     * 管理员计算查询结果总数（包含所有状态）
     */
    @Select("<script>" +
            "SELECT COUNT(*) FROM resources r " +
            "WHERE 1=1 " +
            "<if test='keyword != null and keyword != \"\"'>" +
            "AND (r.title LIKE CONCAT('%', #{keyword}, '%') OR r.description LIKE CONCAT('%', #{keyword}, '%')) " +
            "</if>" +
            "<if test='categoryId != null'>" +
            "AND r.category_id = #{categoryId} " +
            "</if>" +
            "<if test='fileType != null and fileType != \"\"'>" +
            "AND r.file_type = #{fileType} " +
            "</if>" +
            "<if test='status != null and status != \"\"'>" +
            "AND r.status = #{status} " +
            "</if>" +
            "</script>")
    long countAdminResources(@Param("keyword") String keyword,
                            @Param("categoryId") Long categoryId,
                            @Param("fileType") String fileType,
                            @Param("status") String status);
} 